{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 实例1:自动批量生成物料标签"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "作为一个生产型企业，其产品种类繁多。按照客户要求，每种产品在出货时都需要在包装上贴上如下信息标签，以便识别。\n",
    "![](images\\prod_label.png)\n",
    "标签上需要包含6种信息，原始资料内容如下：\n",
    "![](images\\prod_info.png)\n",
    "\n",
    "我们注意到原始信息中的品牌，有些是大写，有些是小写，而标签要求全部大写，因此在填入之前需要先做处理。公司的常规做法是充分发挥人海战术，安排一个小组的人一哄而上，先手动将小写字母替换为大写，然后开始一系列的\"复制\"加\"粘贴\"。通常完成一个物料的标签信息需要35秒，100个物料就需要约1小时。可怜那些操作人员，除了手酸，脖子硬，眼睛疼，再加上键盘上的“ctrl，C，V”三个键像光头一样光滑外，还容易出错。如果原始信息有误，部分标签还得小心翼翼、如履薄冰地重新复制粘贴来修正。有时候，操作人员一走神，复制或者粘贴错了，还会被客户投诉，继而被老板训斥，整个心情都不好了。\n",
    "\n",
    "通常一个订单有上百种物料，每天需要处理约30个订单，需要4个人全职处理这些信息。使用Python, 只需要很少量代码即可轻松搞定，运行只需几秒钟时间。只要原始信息没问题，Python会保质保量完成任务。\n",
    "\n",
    "首先，我们需要将空白标签的格式复制足够多个。为方便打印，本例是在A4纸上每排放3个标签。也可让Python去自动填写标签第一列的信息，但涉及格式操作，还不如在Excel内直接设置方便。\n",
    "\n",
    "以上操作完成后，就可以开始后续的信息读取和填写了，步骤如下：\n",
    "> 1. 从“info”工作表读取所有物料的信息\n",
    "> 2. 转换字母为大写，存储好信息\n",
    "> 3. 将处理好的信息逐个写入到“label”工作表\n",
    "> 4. 保存工作表\n",
    "\n",
    "下面我们需要将自然语言翻译成计算机能理解的语言,以便它按我们的指令执行："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# 从“info”工作表读取并处理信息，存入列表\n",
    "from openpyxl import load_workbook\n",
    "def get_info(path):\n",
    "    wb = load_workbook(path)\n",
    "    ws = wb['info']\n",
    "    info=[]\n",
    "    for row in range(2, ws.max_row+1):\n",
    "        brand = ws['A' + str(row)].value\n",
    "        if brand: # 判断“品牌”是否为空值，若为空值则不进行小写字母的转换\n",
    "                  # 若无这行，当遇到“品牌”为空值时，程序会报错，因为空值无法进行大小写转换\n",
    "            brand=brand.upper() #将“品牌”中的小写字母全部转换成大写字母\n",
    "        typ = ws['B' + str(row)].value # 获取单元格中的数据\n",
    "        pn = ws['C' + str(row)].value\n",
    "        lotno = ws['D' + str(row)].value\n",
    "        date = ws['E' + str(row)].value\n",
    "        if date: # 判断“日期时间”是否为空值，若为空值则不进行日期获取\n",
    "                 # 若无这行，当遇到“日期时间”为空值时，程序会报错，因为空值无法进行日期获取\n",
    "            date=date.date() #只获取日期时间中的日期，比如2019-2-20，不需要具体时间\n",
    "        quantity = str(ws['F' + str(row)].value)+\" pcs\" # 在数量后面加上“pcs”字样\n",
    "        data={\n",
    "            \"brand\":brand,\n",
    "            \"typ\":typ,\n",
    "            \"pn\":pn,\n",
    "            \"lotno\":lotno,\n",
    "            \"date\":date,\n",
    "            \"quantity\":quantity\n",
    "        }\n",
    "        info.append(data)\n",
    "    return info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "finished\n",
      "Wall time: 79 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# %%time 用于查看程序运行所需时间\n",
    "path=\"data\\label_info.xlsx\"\n",
    "info=get_info(path)\n",
    "print(\"finished\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们首先导入`openpyxl`模块中的`load_workbook`模块。`openpyxl`是一个可读取Excel的第三方库。`load_workbook()`可打开一个现有的Excel文件（或叫工作簿）。\n",
    "\n",
    "此处我们先定义一个函数，取名为`get_info`,该函数包含一个参数`path`,用来传入所需要打开的Excel文件的路径。定义函数是为了方便后续反复地调用，因为我们后续需要处理的文件可能在不同的路径，我们只需传入不同的文件路径即可获取该文件的信息，而不用每次都去函数内部做改动。其中`def`就是定义（Define）的意思。函数就是高等数学中的`f(x)`,`x`就是它的参数，我们传入`x`,经函数一运算，就可以得到我们想要的结果。比如`f(x)=x*2`,那假如将`x=2`传入该函数，结果就是`f(2)=2*2=4`。以上`get_info(path)`函数可以理解为，传入“一个Excel文件的路径”，就可以得到“这个Excel内指定的信息”。我们甚至可以设想奶牛就是一个函数：`奶牛(草)=牛奶`,只要给这个奶牛函数传入“草”，就可以得到我们要的结果--“牛奶”。\n",
    "\n",
    "我们使用`load_workbook（path）`读取文件，并存在变量`wb`里面。我们可以想象变量就是容器，用来储存各种数据，这里的`wb`就是这个容器的名字。可以通过`type()`函数查看这个容器的类型。我们可以看到这是一个`openpyxl.workbook.workbook.Workbook`类型的数据，可以理解为它装下了整个Excel文件的内容。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "openpyxl.workbook.workbook.Workbook"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb = load_workbook(\"data\\label_info.xlsx\")\n",
    "type(wb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "但是我们并不需要整个Excel文件的内容，只需要其中一个名叫“info”的工作表的内容，因此我们再定义一个变量（容器）`ws`从容器`wb`中将工作表“info”中的数据取过来并放进去：`ws = wb['info']`。`ws`的类型是`openpyxl.worksheet.worksheet.Worksheet`，可以理解为它装下了工作表“info”中的内容。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "openpyxl.worksheet.worksheet.Worksheet"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ws=wb['info']\n",
    "type(ws)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "然后我们建立了一个空列表`info`来存储从`ws`中按每种物料的信息取出来的数据。然后从第二行开始（第一行是列名）通过`for`循环来逐个查看并获取每行的数据，存进一个字典`data`，再将`data`放入`info`列表中。其中`ws.max_row`可获得这个工作表的行数。比如本例的“info”工作表有71行数据，那这个`ws.max_row`就是71。由于从`range`函数中取值时，是不包含末尾那个数的，所以此处需要`ws.max_row+1`=72，才能最多取到第71行的数据。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "71"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ws.max_row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2\n",
      "3\n",
      "4\n"
     ]
    }
   ],
   "source": [
    "for i in range(2,5):# 从2-5范围内逐个取值，但不包含5\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据提取完成后，我们就可以检查我们的数据是否有问题。`len(info)`可查看info列表中有多少个数据，此处是70个数据，正好与Excel中的数据行数相同。再看info列表中的第一个数据`info[0]`（注意列表的索引是从0开始，0就表示第一个数据，以此类推）：\n",
    "```python\n",
    "{'brand': 'ABC1',\n",
    " 'typ': '11X11-XX761X8',\n",
    " 'pn': '011-0076108',\n",
    " 'lotno': 'Q19H023994901',\n",
    " 'date': datetime.date(2019, 2, 20),\n",
    " 'quantity': '300 pcs'}\n",
    "```\n",
    "字典里的键对应的是“品牌、型号、物料编号、批次号、生成日期、数量”，值正好是Excel表中第2行的数据。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'brand': 'ABC1',\n",
       " 'typ': '11X11-XX761X8',\n",
       " 'pn': '011-0076108',\n",
       " 'lotno': 'Q19H023994901',\n",
       " 'date': datetime.date(2019, 2, 20),\n",
       " 'quantity': '300 pcs'}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "# 以上两行是为了让输出全部显示，如果没有它们，如下Out只会显示最后一个info[0]的结果，而不会显示len(info)=70的结果\n",
    "len(info)\n",
    "info[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在`label_info_nan.xlsx`故意放了3行含有空值的数据，可以看到最后一个数据的“品牌”和“生产日期”是“None”，即为空值。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "91"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "{'brand': 'ABC91',\n",
       " 'typ': 'C1HQERFFX1D',\n",
       " 'pn': '212-05012072',\n",
       " 'lotno': 'H0345345410',\n",
       " 'date': datetime.date(2018, 11, 8),\n",
       " 'quantity': '200 pcs'}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "info_nan=get_info(\"data\\label_info_nan.xlsx\")\n",
    "len(info_nan)\n",
    "info_nan[-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "此时，我们成功获取了所有产品的数据，并存进了列表容器`info`中。下一步就需要将其中的信息逐个提取出来，并写入到标签工作表里去了。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将处理好的信息逐个写入到“label”工作表并保存\n",
    "def write_info(path,info):\n",
    "    wb = load_workbook(path)\n",
    "    ws = wb['label']\n",
    "    k=0\n",
    "    for i in range(2,9,3): #列遍历\n",
    "        for j in range(1,round(len(info)*7/3),7): #行遍历\n",
    "            if k < len(info): #当数据条数不是3的整数倍时，计数器k会超出列表info的范围，后续代码只有在k < len(info)的情况下执行\n",
    "                ws.cell(row=j, column=i).value = info[k]['brand']\n",
    "                ws.cell(row=j+1, column=i).value = info[k]['typ']\n",
    "                ws.cell(row=j+2, column=i).value = info[k]['pn']\n",
    "                ws.cell(row=j+3, column=i).value = info[k]['lotno']\n",
    "                ws.cell(row=j+4, column=i).value = info[k]['date']\n",
    "                ws.cell(row=j+5, column=i).value = info[k]['quantity']\n",
    "            k+=1 #k是列表info的索引，此处加一以便获取下一条数据\n",
    "    wb.save(path) #保存Excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished!\n",
      "Wall time: 557 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "path=\"data\\label_info.xlsx\"\n",
    "write_info(path,info) #调用write_info函数，将数据写入并保存\n",
    "print(\"Finished!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "此处我们定义了第二个函数`write_info`，它包含两个参数，`path`及`info`,即需操作的文件及装着信息的容器。写成函数，方便后续的反复调用。这个函数的功能是将容器里的信息写入到需要操作的文件，并保存，不需要返回什么值，因此没有`return`语句。\n",
    "\n",
    "我们的目的是将`label_info.xlsx`工作簿中的`info`中的信息填写到`label`中去，所以此处还是在`label_info.xlsx`中操作。先用`load_workbook`打开Excel表，然后用`wb`这个容器来存储其所有信息。然后用`ws`容器存储`label`这张工作表里的信息。我们定义了一个变量k,并设定它的初始值为0。这是为了给后续遍历列表`info`提供索引，比如`info[0]`就是指`info`里面的第一条数据，也就是第一个物料的所有信息。然后是`for`循环遍历所有列。为方便打印，我们的标签是3个一行，每个标签之间空一行一列。而我们只给第二、五、八列填写数据，因此这里的`range(2,9,3)`指的是从2列开始，8列结束，以3为步长，列的取值将为`2,5,8`，正是我们需要填写数据的列号。\n",
    "![](images\\prod_label_3_2.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "随后我们进行行遍历，由于每个标签有6个信息，加上1个空行，因此每个标签总共占用了7行。`range(1,round(len(info)*7/3),7)`指从第一行开始，以步长为7取值，直到最后一行,`round`用于四舍五入以保证为整数，此处取的值都是每个标签的第一行。假设共有6个标签需要填写，那么需要的行数为`6*7/3=14`，`range(1,14,7)`可取到的值为1，8行。\n",
    "\n",
    "因为数据可能不是3的整数倍，此时计数器`k`可能会超出列表`Info`的范围导致程序报错而终止，所以加上`if`语句，后续代码只有在`k < len(info)`的情况下才执行。然后就是写入数据了。以第一个标签为例，我们在第一行第二列写入第一个物料的“品牌”信息，第二行第二列写入第一个物料的“型号”信息，一次类推。每写完一个物料的标签信息，计数器k需要加上一个1。因为`k`是列表`info`的索引，我们写好了`info[0]`里面的信息后，就需要接着写`info[1]`的信息了。信息全部写完后，就使用`wb.save`保存Excel文件。所保存的文件可指定新的路径，新的文件名，此处我们还是使用原文件名保存，避免产生太多Excel文件。\n",
    "\n",
    "`%%time`语句可计算并显示运行这个程序所花费的时间，我们可以看到只用了不到短短0.2秒（`Wall time: 137 ms`）就完成了，真真实实的千倍效率提升。最终写好的标签如图所示。\n",
    "![](images\\prod_label_done.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "以上是处理单个订单的标签，对于多个订单的标签，我们可以用类似的方法一次完成。我们只需要将所有订单的Excel文件放入一个文件夹，然后就可以批量操作。此时，需要引入`os`模块。该模块可自动获取文件路径及文件名。我们定义一个函数`file_name`，包含一个参数`file_dir`,该参数用于传入Excel文件所在的路径。随后定义一个列表`names`用于存储后续获取的Excel文件路径。`os.listdir`将返回指定的文件夹包含的文件及文件夹的名字的列表。我们只需要处理Excel文件，因此加入一个条件判断，只将带有“.xlsx”后缀的文件放入`names`列表。由于`os.listdir`只获取了文件名，但我们在写入数据时需要文件的完整路径，所以需要把文件所在的路径加在文件名前面，即`file_dir+file`。最后返回`names`列表，获得所有Excel文件的完整路径。然后就可以调用之前写好的读取信息及写入信息的函数，逐个将`names`列表中的路径传入函数，完成信息的读取和写入。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['data/label_info - 1.xlsx',\n",
       " 'data/label_info - 2.xlsx',\n",
       " 'data/label_info - 3.xlsx',\n",
       " 'data/label_info - 4.xlsx',\n",
       " 'data/label_info - 5.xlsx',\n",
       " 'data/label_info.xlsx',\n",
       " 'data/label_info_nan.xlsx']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "def file_name(file_dir):\n",
    "    names=[]\n",
    "    for file in os.listdir(file_dir):\n",
    "        if \".xlsx\" in file: # 只处理该路径下的Excel文件\n",
    "            names.append(file_dir+file)\n",
    "    return names\n",
    "file_dir=\"data/\"\n",
    "pathess=file_name(file_dir)\n",
    "pathess"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished!\n",
      "Wall time: 1.93 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "for path in pathess:\n",
    "    info=get_info(path)\n",
    "    write_info(path,info) \n",
    "print(\"Finished!\")    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "因为不同的客户，我们的文件夹可能还包含子文件夹，那要一次获取母文件夹中的所有Excel文件，需要应用到`os.walk`函数。`os.walk`可遍历一个目录内各个子目录和子文件。它先遍历当前目录，返回三个值，分别是目录的路径，目录下子目录的名字，文件的名字。再遍历子目录，同样返回子目录的路径，子目录下的子目录的名字，子目录内的文件的名字。若还有子目录，则继续遍历，直到所有目录被遍历。因此需要三个变量`root, dirs, files`去接收它的返回值。\n",
    "\n",
    "我们在`data/`路径下建了2个子目录，a和b，可以看到`os.walk`先遍历`data/`目录，再`data/a`,`data/b`，并找出所有目录下的文件。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "data/\n",
      "******************\n",
      "['a', 'b']\n",
      "******************\n",
      "['label_info - 1.xlsx', 'label_info - 2.xlsx', 'label_info - 3.xlsx', 'label_info - 4.xlsx', 'label_info - 5.xlsx', 'label_info.xlsx', 'label_info_nan.xlsx']\n",
      "data/a\n",
      "******************\n",
      "[]\n",
      "******************\n",
      "['abc.docx', 'label_info - 6.xlsx', 'label_info - 7.xlsx']\n",
      "data/b\n",
      "******************\n",
      "[]\n",
      "******************\n",
      "['new.txt']\n"
     ]
    }
   ],
   "source": [
    "file_dir=\"data/\"\n",
    "for root, dirs, files in os.walk(file_dir):\n",
    "    print(root,dirs,files,sep=\"\\n******************\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['data/label_info - 1.xlsx',\n",
       " 'data/label_info - 2.xlsx',\n",
       " 'data/label_info - 3.xlsx',\n",
       " 'data/label_info - 4.xlsx',\n",
       " 'data/label_info - 5.xlsx',\n",
       " 'data/label_info.xlsx',\n",
       " 'data/label_info_nan.xlsx',\n",
       " 'data/a\\\\abc.docx',\n",
       " 'data/a\\\\label_info - 6.xlsx',\n",
       " 'data/a\\\\label_info - 7.xlsx',\n",
       " 'data/b\\\\new.txt']"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pathss=[] # 文件夹内所有文件（包括子目录）\n",
    "file_dir=\"data/\"\n",
    "for root, dirs, files in os.walk(file_dir):\n",
    "    path = [os.path.join(root, name) for name in files]\n",
    "    pathss.extend(path)\n",
    "pathss"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "由于我们需要获得文件的完整路径，但以上`files`只是文件的名字，所以需要使用`os.path.join`函数将文件所在路径与文件名连起来，如`data/b`需要跟`new.txt`连起来,变成`'data/b\\\\new.txt'`，其中\"\\\\\"是\"\\\"的转义字符。另外上述程序用到了列表解析式:\n",
    "```python \n",
    "path = [os.path.join(root, name) for name in files]```\n",
    "这可以让程序变得非常简洁，也节省运算时间。通过看下面这个小例子就可以知道其原理：\n",
    "```python\n",
    "a=[i for i in range(10)]\n",
    "a\n",
    ">>[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]```\n",
    "如果文件夹内还有除Excel之外的文件，需要将其从文件路径列表中剔除，然后再传入信息读取和写入的函数。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['data/label_info - 1.xlsx',\n",
       " 'data/label_info - 2.xlsx',\n",
       " 'data/label_info - 3.xlsx',\n",
       " 'data/label_info - 4.xlsx',\n",
       " 'data/label_info - 5.xlsx',\n",
       " 'data/label_info.xlsx',\n",
       " 'data/label_info_nan.xlsx',\n",
       " 'data/a\\\\label_info - 6.xlsx',\n",
       " 'data/a\\\\label_info - 7.xlsx']"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result=[]\n",
    "for i in pathss:\n",
    "    if '.xlsx' in i:\n",
    "        result.append(i)\n",
    "result"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
